const pool = require('../utils/pool')
const express = require('express')

const router = express.Router()

// 查询缴费
router.get('/allfee', (req, res, next) => {
  const kw = req.query.kw
  const sql = `
    SELECT 
        zh_fee.generatedTime,
        fee_type.type_name,
        zh_household.householdName,
        zh_household.householdAddr,
        zh_fee.amount
    FROM 
        zh_fee
    JOIN 
        fee_type ON fee_type.id = zh_fee.type
    JOIN 
        zh_household ON zh_household.householdId = zh_fee.householdId
    WHERE 
        ${kw !== 'undefined' ? `fee_type.type_name = '${kw}'` : 1};
  `
  console.log(sql);

  pool.query(sql, (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.length === 0) {
      return res.send({
        code: 4001,
        msg: '查询失败'
      })
    }
    res.send({
      code: 2000,
      msg: '查询成功',
      data: result.map(item => {
        return {
          ...item,
          generatedTime: new Date(item.generatedTime).toLocaleDateString()
        }
      })
    })
  })
})

// 查询缴费类型
router.get('/type', (req, res, next) => {
  const sql = `
  select distinct type_name from fee_type
  `
  pool.query(sql, (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.length === 0) {
      return res.send({
        code: 4001,
        msg: '查询失败'
      })
    }
    res.send({
      code: 2000,
      msg: '查询成功',
      data: result
    })
  })
})

// 缴费查询
router.get('/fee/query', (req, res, next) => {
  const status = req.query.status || undefined
  const start = req.query.start || 1
  const count = Number(req.query.count) || 10
  const startDate = req.query.startDate || 0
  const endDate = req.query.endDate

  const sql = `
    select 
      zh_fee.fid,
      zh_fee.generatedTime,
      zh_fee.paidTime,
      fee_type.type_name, 
      zh_household.householdAddr,
      zh_fee.amount,
      zh_household.householdName,
      zh_fee.payment_status
    from zh_fee
    left join fee_type on fee_type.id = zh_fee.type 
    left join zh_household on zh_household.householdId = zh_fee.householdId 
    where ${status === undefined ? 1 : `zh_fee.payment_status = '${status}'`}
    order by zh_fee.generatedTime desc, zh_fee.paidTime desc
    limit ?, ?;
    select count(*) as total from zh_fee where ${status === undefined ? 1 : `zh_fee.payment_status = '${status}'`};
    `
  //   const sql = `
  //   select 
  //     zh_fee.fid,
  //     zh_fee.generatedTime,
  //     zh_fee.paidTime,
  //     fee_type.type_name, 
  //     zh_household.householdAddr,
  //     zh_fee.amount,
  //     zh_household.householdName,
  //     zh_fee.payment_status
  //   from zh_fee
  //   left join fee_type on fee_type.id = zh_fee.type 
  //   left join zh_household on zh_household.householdId = zh_fee.householdId 
  //   where ${status === undefined ? '1=1' : `zh_fee.payment_status = '${status}'`}
  //   AND (
  //     IFNULL(zh_fee.generatedTime, zh_fee.paidTime) >= '${new Date(startDate).toISOString().slice(0, 19).replace('T', ' ')}'
  //     ${endDate ? `AND IFNULL(zh_fee.generatedTime, zh_fee.paidTime) <= '${new Date(endDate).toISOString().slice(0, 19).replace('T', ' ')}'` : ''}
  //   )
  //   order by zh_fee.generatedTime desc, zh_fee.paidTime desc
  //   limit ?, ?;

  //   select count(*) as total from zh_fee where ${status === undefined ? '1=1' : `zh_fee.payment_status = '${status}'`}
  //   AND (
  //     IFNULL(zh_fee.generatedTime, zh_fee.paidTime) >= '${new Date(startDate).toISOString().slice(0, 19).replace('T', ' ')}'
  //     ${endDate ? `AND IFNULL(zh_fee.generatedTime, zh_fee.paidTime) <= '${new Date(endDate).toISOString().slice(0, 19).replace('T', ' ')}'` : ''}
  //   )
  //   ;
  // `;
  pool.query(sql, [(start - 1) * count, count], (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.length === 0) {
      return res.send({
        code: 4001,
        msg: '查询失败'
      })
    }
    res.send({
      code: 2000,
      msg: '查询成功',
      data: result[0].map(item => ({
        ...item,
        generatedTime: item.generatedTime && new Date(item.generatedTime).toLocaleDateString(),
        paidTime: item.paidTime && new Date(item.paidTime).toLocaleDateString()
      })),
      total: result[1][0]['total'],
      start: Number(start),
      count
    })
  })
})
module.exports = router